package cn.ciis.basic.base;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.BigExcelWriter;
import cn.hutool.poi.excel.ExcelUtil;
import com.jfinal.kit.*;
import com.jfinal.plugin.activerecord.*;

import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author 冉志林
 */
public abstract class BaseService {

    public static final String OPERATION_SUCCESSFUL = "操作成功";
    public static final String OPERATION_FAILED = "操作失败";

    /**
     * 成功信息 操作成功
     *
     * @return
     */
    public Ret success() {
        return Ret.ok("msg", OPERATION_SUCCESSFUL);
    }

    /**
     * 失败消息
     *
     * @return
     */
    public Ret fail() {
        return Ret.fail("msg", OPERATION_FAILED);
    }

    /**
     * 获取model dao
     *
     * @return
     */
    public abstract Model<Model> getDao();

    protected Table _getTable() {
        if (null == getDao()) {
            LogKit.error("请先实现getDao()方法，并且不能返回null");
        }
        return TableMapping.me().getTable(getDao().getClass());
    }

    /**
     * 获取表名称
     *
     * @return
     */
    public String getTable() {
        return _getTable().getName();
    }

    /**
     * 获取分页sql
     * @return
     */
    public String getTablePageSql(){
        return Db.getSql(""+getTable()+".page");
    }


    /**
     * 根据id查询
     *
     * @param id
     * @return
     */
    public Model findById(Object id) {
        return getDao().findById(id);
    }

    /**
     * 查询当前表所有数据
     *
     * @return
     */
    public List<Model> findAll() {
        return getDao().findAll();
    }

    /**
     * 通用保存或者修改
     *
     * @param model
     * @return
     */
    public Ret saveOrUpdate(Model<?> model) {
        if (null == model.get("id")) {
            return model.save() ? success() : fail();
        } else {
            return model.update() ? success() : fail();
        }
    }


    /**
     * 根据id单条删除
     *
     * @param id
     * @return
     */
    public Ret delete(int id) {
        return getDao().deleteById(id) ? success() : fail();
    }

    /**
     * 批量删除、单条数据删除、级联数据删除
     *
     * @param ids
     * @param sql
     * @return
     */
    public Ret delete(String ids, String... sql) {
        Db.tx(
                () -> {
                    String[] split = ids.split(",");
                    for (int i = 0; i < split.length; i++) {
                        if (StrKit.notBlank(sql)) {
                            for (int j = 0; j < sql.length; j++) {
                                Db.delete(sql[j], split[i]);
                            }
                        }
                        getDao().deleteById(split[i]);
                    }
                    return true;
                });
        return success();
    }

    /**
     * 多条id删除
     *
     * @param ids
     * @return
     */
    public Ret deletes(Object ids) {
        return Db.update("delete from " + getTable() + " where id in(" + ids + ")") > 0
                ? success()
                : fail();
    }

    /**
     * 逻辑删除
     * @param ids
     * @param filed
     * @param val
     * @return
     */
    public Ret logicalDeletion(Object ids,String filed,String val){

        String sql = StrUtil.format("update {} set {} = ? where id IN (?)", getTable(), filed);

         return Db.update(sql, val,ids)>0?success():fail();
    }

    /**
     * 根据多条id删除
     *
     * @param ids
     * @return
     */
    public Ret delete(Object ids) {
        return getDao().deleteByIds(ids) ? success() : fail();
    }

    /**
     * 根据kv值删除数据
     *
     * @param key
     * @param value
     * @return
     */
    public int deltePk(String key, String value) {
        return Db.delete("delete from " + getTable() + " where " + key + " = '" + value + "'");
    }

    /**
     * 获取sql模板的db模式
     *
     * @param key
     * @param kv
     * @return
     */
    public DaoTemplate<Model> dbTemplate(String key, Kv kv) {
        return getDao().template(key, kv);
    }

    /**
     * 判断是否为空
     *
     * @param obj
     * @return
     */
    public boolean isObjectNotEmpty(Object obj) {
        return ObjectUtil.isNotEmpty(obj);
    }


    /**
     * 获取表字段
     *
     * @return
     */
    public List<Record> getTableFields() {
        return Db.find("SELECT DISTINCT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=?", getTable());
    }


    /**
     * 分页查询 DbTemplate模式
     *
     * @param pageNumber
     * @param pageSize
     * @param model
     * @return
     */
    public Page paginate(int pageNumber, int pageSize, Model<?> model) {
        Kv cond = Kv.create();
        getTableFields().forEach(findList -> {
            String columnName = findList.getStr("COLUMN_NAME");
            Object objColumnName = model.get(columnName);
            if (isObjectNotEmpty(objColumnName)) {
                cond.set(columnName, objColumnName);
            }
        });
        return dbTemplate(getTable() + ".page", Kv.by("cond", cond)).paginate(pageNumber, pageSize);
    }

    /**
     * dao 自己定义方法名称的分页方法
     *
     * @param pageNumber
     * @param pageSize
     * @param model
     * @param method
     * @return
     */
    public Page<?> paginate(int pageNumber, int pageSize, Model<?> model, String method) {
        Kv cond = Kv.create();
        getTableFields().forEach(findList -> {
            String columnName = findList.getStr("COLUMN_NAME");
            Object objColumnName = model.get(columnName);
            if (isObjectNotEmpty(objColumnName)) {
                cond.set(columnName, objColumnName);
            }
        });
        return dbTemplate(getTable() + "." + method, Kv.by("cond", cond)).paginate(pageNumber, pageSize);
    }

    /**
     * db方式的分页方法
     *
     * @param pageNumber 当前页
     * @param pageSize   每页大小
     * @param kv         条件
     * @param method     sql模板名称 xx.xx
     * @return
     */
    public Page<?> pageinate(int pageNumber, int pageSize, Kv kv, String method) {
        return dbTemplate(method, Kv.by("cond", kv)).paginate(pageNumber, pageSize);
    }

    /**
     * excle 导出
     *
     * @param fileName
     * @param aThs
     * @param aTds
     * @param ids
     * @return
     */
    public File export(String fileName, String aThs, String aTds, String ids,String... sqlmodel) {
        if (StrKit.isBlank(fileName) || StrKit.isBlank(aThs) || StrKit.isBlank(aTds)) {
            return null;
        }
        String sql="";
        if (StrKit.notBlank(sqlmodel)){
            sql="select "+aTds+" from ("+sqlmodel[0]+") a ";
            if (StrKit.notBlank(ids)){
                sql+=" where a.id in(" + ids + ")";
            }
        }else{
            sql = "select " + aTds + " from " + getTable();
            int createAc = sql.indexOf("createAc");
            if (createAc > 0) {
                sql = sql.replace("createAc", "(select fullName from sys_account where id=createAc) as createAc");
            }
            if (StrKit.notBlank(ids)) {
                sql+=" where id in(" + ids + ")";
            }
        }
        List<Record> list = Db.find(sql);
        ArrayList<Map<String, Object>> rows = CollUtil.newArrayList();
        list.forEach(re -> {
            rows.add(re.getColumns());
        });
        String filePath = PathKit.getWebRootPath() + File.separator + "download" + File.separator + "export" + File.separator + fileName + ".xlsx";
        BigExcelWriter bigExcelWriter = ExcelUtil.getBigWriter(filePath);
        String[] splitAth = aThs.split(",");
        String[] splitAtd = aTds.split(",");
        for (int j = 0; j < splitAtd.length; j++) {
            bigExcelWriter.addHeaderAlias(splitAtd[j].trim(), splitAth[j].trim());
        }
        bigExcelWriter.write(rows, true);
        bigExcelWriter.close();
        File file = new File(filePath);
        if (file.isFile()) {
            return file;
        } else {
            return null;
        }
    }


    /**
     * 根据表字段修改
     *
     * @param table
     * @param id
     * @param field
     * @param value
     * @return
     */
    public Ret updatePk(String table, String id, String field, String value) {
        int update = Db.update("update " + table + " set `" + field + "`=" + value + " where id=" + id);
        if (update > 0) {
            return success();
        } else {
            return fail();
        }
    }

    /**
     * 查询最大id的数据
     *
     * @return
     */
    public Model<?> findMaxId() {
        return getDao()
                .findFirst(
                        "select * from " + getTable() + " where id=(select max(id) from " + getTable() + ")");
    }

    /**
     * 获取sql语句并得出字段注释 格式为：[{value=, key=id}, {value=, key=userName}, {value=,
     * key=status}, {value=, key=createAt}, {value=, key=ip}, {value=, key=avatar}]
     *
     * @return
     */
    public List<Object> getFieldAndNotes() {
        String sql = Db.getSql(getTable() + ".page");
        String strSql = sql.toLowerCase();
        int from = strSql.lastIndexOf("from");
        String substring = sql.substring(7, from);
        String s = substring.replaceAll(" ", "").replaceAll("`", "");
        List<Record> records =
                Db.find(
                        "SELECT DISTINCT COLUMN_NAME,COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=? and table_schema='j_mis_1.0.4'",
                        getTable());
        String[] split = s.split(",");
        List<Object> list = new ArrayList<>();
        Map<String, Object> map = null;
        for (int j = 0; j < split.length; j++) {
            for (int i = 0; i < records.size(); i++) {
                if (records.get(i).get("COLUMN_NAME").equals(split[j])) {
                    map = new HashMap<String, Object>();
                    map.put("key", split[j]);
                    map.put("value", records.get(i).get("COLUMN_COMMENT"));
                    list.add(map);
                }
            }
        }
        return list;
    }

    /**
     * 获取当前表的字段返回字符串
     *
     * @return
     */
    public String tableFiled(String name) {
        String sql =
                "SELECT DISTINCT GROUP_CONCAT(COLUMN_COMMENT)COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=? AND COLUMN_NAME=?";
        String table = getTable();
        Record first = Db.findFirst(sql, table, name.replace("]", "").replace(" ", ""));
        return first.getStr("COLUMN_COMMENT");
    }


    /**
     * 根据条件查询后排序
     *
     * @param map
     * @return
     */
    public List<?> findByFiled(Map<String, Object> map, String filed,String order) {
        String sql = "select * from " + getTable() + " where 1=1 ";
        for (String key : map.keySet()) {
            sql += " and " + key + " = '" + map.get(key).toString() + "'";
        }
        if (StrKit.notBlank(filed)){
            sql += " order by " + filed+" "+order;
        }
        return getDao().find(sql);
    }

    public List<?> findByFiled(Map<String, Object> map, String filed) {
        String sql = "select * from " + getTable() + " where 1=1 ";
        for (String key : map.keySet()) {
            sql += " and " + key + " = '" + map.get(key).toString() + "'";
        }
        return getDao().find(sql + " order by " + filed);
    }

    public Model<?> findByFiled(String key, String value) {
        String sql = "select * from " + getTable() + " where " + key + " = '" + value + "'";
        return getDao().findFirst(sql);
    }

    /**
     * 导出特殊字段处理
     */
    public String[] column(String fieldNames, String titles) {
        String[] split = replaceStr(fieldNames);
        String[] title = replaceStr(titles);
        for (int i = 0; i < split.length; i++) {
            String fieldName = split[i];
            String s = tableFiled(fieldName);
            if (StrKit.notBlank(s) && s.indexOf("-") != -1) {
                title[i] = s;
            }
        }
        return title;
    }

    /**
     * 数据导出时候转换
     *
     * @param str
     * @return
     */
    public String[] replaceStr(String str) {
        if (StrKit.notBlank(str)) {
            String s = str.replace("[0,", "").replaceAll("[^\\w,\\u4E00-\\u9FA5]", "");
            return s.split(",");
        }
        return null;
    }

}
